import pymysql as sql
import tushare as ts
import baostock as bs
import datetime
import time
d = str(datetime.datetime.now().year)+"-"+str(datetime.datetime.now().month)\
    +"-"+str(datetime.datetime.now().day)
def get_idlist():
    db = sql.connect(host='localhost', port=3306, user='root', password="254001fangfeifei")
    cur = db.cursor()
    cur.execute('use stock')
    cur.execute('select * from idlist')
    data = cur.fetchall()
    #[[id,name,status],...]
    return data
'''
print(d,type(d))
bs.login()
df = bs.query_all_stock(d).get_data()
cur.execute("use stock")
for i in range(df.shape[0]):
    cur.execute("insert into idlist(id,name,status) values(%s,%s,%s) ",[df.iloc[i][0],\
                                                                        df.iloc[i][2].replace('指数',''),df.iloc[i][1],])
    db.commit()
db.close()
'''
if __name__ == "__main__":
    import pandas as pd
    lg = bs.login()
    # 显示登陆返回信息
    print('login respond error_code:' + lg.error_code)
    print('login respond  error_msg:' + lg.error_msg)

    #### 获取证券信息 ####
    rs = bs.query_all_stock(day="2017-06-30")
    print('query_all_stock respond error_code:' + rs.error_code)
    print('query_all_stock respond  error_msg:' + rs.error_msg)

    #### 打印结果集 ####
    data_list = []
    while (rs.error_code == '0') & rs.next():
        # 获取一条记录，将记录合并在一起
        data_list.append(rs.get_row_data())
    df = pd.DataFrame(data_list, columns=rs.fields)
    db = sql.connect(host='localhost', port=3306, user='root', password="254001fangfeifei")
    cur = db.cursor()
    cur.execute('use stock')

    #### 结果集输出到csv文件 ####
    #result.to_csv("D:\\all_stock.csv", encoding="gbk", index=False)
    print(df.iloc[1][0],df.iloc[1][1],df.iloc[1][2])
    for i in range(df.shape[0]):
        cur.execute("insert into allid(id,name,status) values(%s,%s,%s) ", [df.iloc[i][0], \
                                                                             df.iloc[i][2].replace('指数', ''),
                                                                             df.iloc[i][1], ])
        db.commit()
    db.close()
    #### 登出系统 ####
    bs.logout()





bs.logout()